import os
import re

import numpy as np
import openpyxl


# 则试团队的工时填写记录在"质量管理及测试支持部"部门，项目名称与业务部门的项目名称一致

class CostMan:
    def __init__(self, dir, dept):
        self.__dir = dir
        self.__dept = dept
        self.__project_month_staff_time = {}
        # 创建2行14列的空数组
        self.__worktimes = np.empty((2,14), dtype=object)
        self.__staff_grade = {}
        self.__grade_price = {}
        # 创建2行14列的空数组
        self.__workmoney = np.empty((1,14), dtype=object)
        self.__month_days = {'01月':'19',
                             '02月':'19',
                             '03月':'21',
                             '04月':'22',
                             '05月':'',
                             '06月':'',
                             '07月':'',
                             '08月':'',
                             '09月':'',
                             '10月':'',
                             '11月':'',
                             '12月':''}
        # excle原始化数据
        self.__orign_data = np.empty((2,5), dtype=object)
        # 资管应用研发部项目（包含测试）
        self.__dept1_projects = []
        self.__dept2_projects = []

    def input(self):
        self.input_1()
        self.input_2()

    # 输入原始工时文件数据
    def input_1(self):
        for folderName, subfolders, filenames in os.walk(self.__dir):
            for filename in filenames:
                split_names = os.path.basename(filename).split('.')
                excel_name = split_names[0]
                ext_name = split_names[1]
                if ext_name == 'xlsx' and re.compile(r'_\w{2}').search(filename)!=None and (re.compile(r'_\w{2}').search(filename).group() == '_外勤' or re.compile(r'_\w{2}').search(filename).group() == '_内勤'):
                    workbook = openpyxl.load_workbook(self.__dir + "/" + filename)
                    worksheet = workbook[excel_name]
                    month = re.compile(r'\d{2}月').search(filename).group()
                    # 外包
                    if re.compile(r'_\w{2}').search(filename).group() == '_外勤':
                        project_ix = 11
                        staff_ix = 1
                        time_ix = 14
                        dept_ix = 10
                    else:
                        # 内部
                        project_ix = 5
                        staff_ix = 1
                        time_ix = 10
                        dept_ix = 7
                    for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
                        new_row = np.empty(5, dtype=object)
                        new_row[0] = row[dept_ix].value
                        new_row[1] = row[project_ix].value
                        new_row[2] = month
                        new_row[3] = row[time_ix].value
                        new_row[4] = row[staff_ix].value
                        # test
                        if (new_row[0] == '资管应用研发部' or new_row[0]=='质量管理及测试支持部' or new_row[0]=='集团数智技术研发中心质量管理部'): # test
                            self.__orign_data = np.vstack((self.__orign_data, new_row))
                        if(new_row[0]=='集团数智技术研发中心质量管理部'):
                            print(new_row[0])
        #print(self.__orign_data)

    def input_100(self):
        for folderName, subfolders, filenames in os.walk(self.__dir):
            for filename in filenames:
                split_names = os.path.basename(filename).split('.')
                excel_name = split_names[0]
                ext_name = split_names[1]
                if ext_name == 'xlsx' and re.compile(r'_\w{2}').search(filename) != None and (
                        re.compile(r'_\w{2}').search(filename).group() == '_外勤' or re.compile(r'_\w{2}').search(filename).group() == '_内勤'):
                    workbook = openpyxl.load_workbook(self.__dir + "/" + filename)
                    worksheet = workbook[excel_name]
                    month = re.compile(r'\d{2}月').search(filename).group()
                    # 外包
                    if re.compile(r'_\w{2}').search(filename).group() == '_外勤':
                        project_ix = 11
                        staff_ix = 1
                        time_ix = 14
                        dept_ix = 10
                    else:
                        # 内部
                        project_ix = 5
                        staff_ix = 1
                        time_ix = 10
                        dept_ix = 7
                    for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
                        if self.__dept != row[dept_ix].value:
                            continue
                        project = row[project_ix].value
                        if project == '社保基金会大数据项目（第二阶段）' or project == '社保基金会大数据基础平台与数据分析平台（二期）':
                            continue
                        time = row[time_ix].value
                        staff = row[staff_ix].value
                        if project not in self.__project_month_staff_time.keys():
                            self.__project_month_staff_time[project] = {month: {staff: time}}
                        elif month not in self.__project_month_staff_time[project].keys():
                            self.__project_month_staff_time[project][month] = {staff: time}
                        elif staff not in self.__project_month_staff_time[project][month].keys():
                            self.__project_month_staff_time[project][month][staff] = time
                        else:
                            self.__project_month_staff_time[project][month][staff] = round(
                                (float(time) + float(self.__project_month_staff_time[project][month][staff])), 2)
        #print(self.__project_month_staff_time)

    # 输入人员职级成本数据
    def input_2(self):
        for folderName, subfolders, filenames in os.walk(self.__dir):
            for filename in filenames:
                split_names = os.path.basename(filename).split('.')
                excel_name = split_names[0]
                ext_name = split_names[1]
                if ext_name == 'xlsx' and excel_name == '人员职级成本':
                    workbook = openpyxl.load_workbook(self.__dir + "/" + filename)
                    worksheet1 = workbook['人员职级']
                    worksheet2 = workbook['职级成本']
                    for row in worksheet1.iter_rows(min_row=2, max_col=worksheet1.max_column, max_row=worksheet1.max_row):
                        self.__staff_grade[row[0].value] = row[1].value
                    #print(self.__staff_grade)
                    for row in worksheet2.iter_rows(min_row=2, max_col=worksheet2.max_column, max_row=worksheet2.max_row):
                        self.__grade_price[row[1].value] = row[2].value
                    #print(self.__grade_price)

    def process(self):
        self.process1()
        self.process2()
        self.process3()
        self.process4()
        self.process5()

    # 统计部门1所有项目
    def process1(self):
        # Numpy遍历行很简单
        for row in self.__orign_data:
            dept = row[0]
            project = row[1]
            if (dept is not None and dept in ('资管应用研发部')) and (project is not None and project not in self.__dept1_projects) :
                self.__dept1_projects.append(project)
        #print(self.__dept1_projects)

    # 统计测试部门的项目
    def process2(self):
        for row in self.__orign_data:
            dept = row[0]
            project = row[1]
            if (dept is not None and dept in ('质量管理及测试支持部','集团数智技术研发中心质量管理部')) and (project is not None and project in self.__dept1_projects):
                self.__dept2_projects.append(project)
        print(self.__dept2_projects)

    #
    def process3(self):
        for row in self.__orign_data:
            dept = row[0]
            project = row[1]
            month = row[2]
            time = row[3]
            staff = row[4]
            if project == '社保基金会大数据项目（第二阶段）' or project == '社保基金会大数据基础平台与数据分析平台（二期）':
                continue
            if (dept=='资管应用研发部' or dept=='质量管理及测试支持部' or dept=='集团数智技术研发中心质量管理部') and (project is not None and (project in self.__dept1_projects or project in self.__dept2_projects)):
                if project not in self.__project_month_staff_time.keys():
                    self.__project_month_staff_time[project] = {month: {staff: time}}
                elif month not in self.__project_month_staff_time[project].keys():
                    self.__project_month_staff_time[project][month] = {staff: time}
                elif staff not in self.__project_month_staff_time[project][month].keys():
                    self.__project_month_staff_time[project][month][staff] = time
                else:
                    self.__project_month_staff_time[project][month][staff] = round(
                        (float(time) + float(self.__project_month_staff_time[project][month][staff])), 2)
        print(self.__project_month_staff_time)

    # 处理人员维度的工时数据
    def process4(self):
        head = ['项目名称','姓名','01月','02月','03月','04月','05月','06月','07月','08月','09月','10月','11月','12月']
        self.__worktimes = np.array(head)
        for project in self.__project_month_staff_time.keys():
            for month in self.__project_month_staff_time[project].keys():
                for staff in self.__project_month_staff_time[project][month].keys():
                    if self.__worktimes.ndim == 2 :
                        rows_ix = np.where((self.__worktimes[:, 0] == project) & (self.__worktimes[:, 1] == staff))[0]
                        if len(rows_ix) > 0:
                            row = self.__worktimes[rows_ix[0]]
                            row[0] = project
                            row[1] = staff
                            row[head.index(month)] = self.__project_month_staff_time[project][month][staff]
                        else:
                            row = np.empty(14, dtype=object)
                            row[0] = project
                            row[1] = staff
                            row[head.index(month)] = self.__project_month_staff_time[project][month][staff]
                            self.__worktimes = np.vstack((self.__worktimes, row))
                    else:
                        row = np.empty(14, dtype=object)
                        row[0] = project
                        row[1] = staff
                        row[head.index(month)] = self.__project_month_staff_time[project][month][staff]
                        self.__worktimes = np.vstack((self.__worktimes, row))
        print(self.__worktimes)

    # 处理人员维度的人务成本数据
    def process5(self):
        head = ['项目名称','姓名','01月','02月','03月','04月','05月','06月','07月','08月','09月','10月','11月','12月']
        self.__workmoney = np.array(head)
        for project in self.__project_month_staff_time.keys():
            for month in self.__project_month_staff_time[project].keys():
                for staff in self.__project_month_staff_time[project][month].keys():
                    # 当前月天数
                    this_month_days = self.__month_days[month]
                    # 当前人员填写工时
                    days = self.__project_month_staff_time[project][month][staff]
                    # 人月
                    person_month = float(days) / float(this_month_days)
                    # 当前职级
                    person_grade = self.__staff_grade[staff]
                    # 当前人月成本
                    person_money = self.__grade_price[person_grade]
                    if self.__workmoney.ndim == 2 :
                        rows_ix = np.where((self.__workmoney[:, 0] == project) & (self.__workmoney[:, 1] == staff))[0]
                        if len(rows_ix) > 0:
                            row = self.__workmoney[rows_ix[0]]
                            row[0] = project
                            row[1] = staff
                            row[head.index(month)] = round((float(person_money)/12 * float(person_month)), 2)
                        else:
                            row = np.empty(14, dtype=object)
                            row[0] = project
                            row[1] = staff
                            row[head.index(month)] = round((float(person_money)/12 * float(person_month)), 2)
                            self.__workmoney = np.vstack((self.__workmoney, row))
                    else:
                        row = np.empty(14, dtype=object)
                        row[0] = project
                        row[1] = staff
                        row[head.index(month)] = round((float(person_money)/12 * float(person_month)), 2)
                        self.__workmoney = np.vstack((self.__workmoney, row))
        print(self.__workmoney)


    def output1(self):
        wb = openpyxl.Workbook()
        ws1 = wb.active
        ws1.title = '原始数据'
        for i in range(0, self.__orign_data.shape[0]):
            ws1.append(self.__orign_data[i].tolist())
        wb.save("原始数据.xlsx")
        wb.close()

    def output2(self):
        wb = openpyxl.Workbook()
        ws1 = wb.active
        ws1.title = '工时成本'
        for i in range(0, self.__worktimes.shape[0]):
            ws1.append(self.__worktimes[i].tolist())
        ws2 = wb.create_sheet('人力成本')
        for i in range(0, self.__workmoney.shape[0]):
            ws2.append(self.__workmoney[i].tolist())
        wb.save("项目成本.xlsx")
        wb.close()

#wt = CostMan('/Users/zhangxiangbin/wps-sync/02 招商金科/03项目管理/2024年/工时填报', '资管应用研发部')
wt = CostMan('./worktime/', '资管应用研发部')
wt.input()
wt.process()
wt.output2()


